﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using vHCS.HR.Entities;
using vHCS.HR.Business;
using Core.Controls.Message;
using System.Data;
using Telerik.Web.UI;
using Core.Message.Entities;
using Core.ExceptionHandling;
using vHCS.LS.Business;
using Core.Common.Session;
using Aspose.Cells;
using System.Collections;
using System.IO;
using System.Configuration;
using vHCS.SYS.Entities;
using K2CoreLib.DataObject;
using K2CoreLib.DataProvider;

namespace vHCS.K2App.MdlK2
{
    public partial class Ter_ExitFormRequest : Core.NTier.UserInterface.BaseFormNotPer
    {
        #region Const
        private const string _EMP_ID = "EmpID";
        private const string _List_Page = "~/MdlHR/TER_TerminalList.aspx";
        private const string _FileName = "ExitForm-Export.xls";
        protected const string _TicketName__ = "Exit Checklist";
        private List<ExitTypeApproveDto> listApprove = null;
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                K2CoreLib.DataProvider.ExitChecklistAction exitAction = new K2CoreLib.DataProvider.ExitChecklistAction();
                listApprove = exitAction.GetUserApprover(Convert.ToInt64(EmpHeader.EmployeeId).ToString());
                if (!IsPostBack)
                {
                    this.EmpHeader.LoadEmpInfo(SessionManager.UserDomain);
                    this.LoadInfo(Convert.ToInt64(EmpHeader.EmployeeId));
                    HR_WorkingRecordBL bl = new HR_WorkingRecordBL();
                    HR_WorkingRecordCollection cl = new HR_WorkingRecordCollection();
                    bl = cl.SelectLastWRByEmpID(EmpHeader.EmployeeId);
                    //if (bl.DTO != null)
                    //    lblITOther.Text = lblITOther.Text.Replace("$Seat$", bl.Seat);

                    ClientScript.RegisterStartupScript(Page.GetType(), "LoadMaximize", "LoadMaximize();", true);
                    LoadEmail();

                    #region Lấy thông tin HRBP
                    K2Collection col_k2 = new K2Collection();
                    HR_EmpCVCollection cl_empcv = new HR_EmpCVCollection();
                    string HRBP = col_k2.GetUserOfHRP_Province(EmpHeader.EmployeeId);
                    string NameHRBP = cl_empcv.GetDisplayNameByUser(HRBP);
                    long EmpID_HRBP = cl_empcv.GetEmpIdByUsername(HRBP);
                    string ext = "";
                    if (EmpID_HRBP > 0)
                    {
                        bl = cl.SelectLastWRByEmpID_ForFormWR1(EmpID_HRBP);
                        if (bl.DTO != null)
                        {
                            ext = bl.ExtNo;
                        }
                    }

                    hdfConsult.Value = lblConsult.Text.Trim();
                    lblConsult.Text = lblConsult.Text.Replace("$HRBP$",NameHRBP);
                    lblConsult.Text = lblConsult.Text.Replace("$ext$",string.IsNullOrEmpty(ext)?"":ext.Trim());
                    #endregion
                }

                
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
        }

        /// <summary>
        /// DungN2
        /// Lấy thông tin email bên lý lịch cá nhân của nhân viên
        /// </summary>
        public void LoadEmail()
        {
            try
            {
                HR_EmpCVBL bl_emp = new HR_EmpCVBL();
                HR_EmpCVCollection cl_emp = new HR_EmpCVCollection();
                bl_emp = cl_emp.SelectByID(EmpHeader.EmployeeId);

                if (bl_emp.DTO != null)
                {
                    txtEmail.Text = bl_emp.Email;
                }
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
        }

        /// <summary>
        /// xử lý sự kiện khi thay đổi nhân viên khác
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void EmpHeader_OnUserChanged(object sender, EventArgs e)
        {
            try
            {
                
                if (this.EmpHeader.EmployeeId > 0)
                {
                    hdfEmpId.Value = this.EmpHeader.EmployeeId.ToString();
                    this.LoadInfo(this.EmpHeader.EmployeeId);

                    LoadEmail();

                    radBenifit_Y.Checked = true;

                    ClientScript.RegisterStartupScript(Page.GetType(), "Consult", "CheckConsult();", true);

                    #region Lấy thông tin HRBP
                    HR_WorkingRecordBL bl = new HR_WorkingRecordBL();
                    HR_WorkingRecordCollection cl = new HR_WorkingRecordCollection();

                    K2Collection col_k2 = new K2Collection();
                    HR_EmpCVCollection cl_empcv = new HR_EmpCVCollection();
                    string HRBP = col_k2.GetUserOfHRP_Province(EmpHeader.EmployeeId);
                    string NameHRBP = cl_empcv.GetDisplayNameByUser(HRBP);
                    long EmpID_HRBP = cl_empcv.GetEmpIdByUsername(HRBP);
                    string ext = "";
                    if (EmpID_HRBP > 0)
                    {
                        bl = cl.SelectLastWRByEmpID_ForFormWR1(EmpID_HRBP);
                        if (bl.DTO != null)
                        {
                            ext = bl.ExtNo;
                        }
                    }
                    lblConsult.Text = hdfConsult.Value;
                    lblConsult.Text = lblConsult.Text.Replace("$HRBP$", NameHRBP);
                    lblConsult.Text = lblConsult.Text.Replace("$ext$", string.IsNullOrEmpty(ext) ? "" : ext.Trim());
                    #endregion

                    
                }
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
        }
        /// <summary>
        /// Load thông tin eixt form tương ứng với emp id
        /// </summary>
        /// <param name="empId"></param>id nhân viên theo kiểu long
        protected void LoadInfo(long empId)
        {
            try
            {
                TER_ExitFormCollection cl = new TER_ExitFormCollection();
                K2Collection colK2 = new K2Collection();
                HR_WorkingRecordBL bl_wr = new HR_WorkingRecordBL();
                HR_WorkingRecordCollection cl_wr = new HR_WorkingRecordCollection();
                HIE_EmpInHierarchyCollection cl_emphie = new HIE_EmpInHierarchyCollection();
                HIE_HierarchyBL bl_hie = new HIE_HierarchyBL();
                HIE_HierarchyCollection cl_hie = new HIE_HierarchyCollection();
                DataTable dat = new DataTable();
                dat = cl.SelectByEmpId(empId);

                #region Lấy thông tin Dept Head có thuộc FA Other
                //Lấy thông tin WR hiện tại của nhân viên
                bl_wr = cl_wr.SelectLastWRByEmpID(empId);

                long hierarchyid = 0;
                if (bl_wr.DTO != null)
                    hierarchyid = bl_wr.HierarchyId;

                //

                //Lấy thông tin Dept Head của nhân viên
                long DeptID = cl_emphie.SelectDeptHie(hierarchyid);

                bool CheckFAOther = colK2.CheckDeptFA(DeptID.ToString());
                hdfCheckFAOther.Value = CheckFAOther.ToString();
                //

                #endregion

                if (dat.Rows.Count > 0)
                {
                    this.txtStartDate.Text = DateTime.Parse(dat.Rows[0]["JoinDate"].ToString().Trim()).ToShortDateString();
                    if (dat.Rows[0]["LastWorkingDate"].ToString().Trim() != "")
                    {
                        this.txtEndDate.Text = DateTime.Parse(dat.Rows[0]["LastWorkingDate"].ToString().Trim()).ToShortDateString();
                    }

                    dtsExitList.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsExitList.CacheClear();
                    dtsFAExit.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsFAExit.CacheClear();
                    dtsFAExitOther.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsFAExitOther.CacheClear();
                    dtsOtherHRExit.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsOtherHRExit.CacheClear();
                    dtsAFExit.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsAFExit.CacheClear();
                    dtsAFExitOther.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsAFExitOther.CacheClear();
                    dtsHRExit.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsHRExit.CacheClear();
                    dtsITExit.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsITExit.CacheClear();
                    dtsITOther.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsITOther.CacheClear();
                    dtsHRTraining.SelectParameters["empId"].DefaultValue = EmpHeader.EmployeeId.ToString();
                    dtsHRTraining.CacheClear();

                    dtgIT.DataBind();
                    dtgITOther.DataBind();
                    dtgExitList.DataBind();
                    dtgHROtherExit.DataBind();
                    dtgFAExit.DataBind();
                    dtgFAExitOther.DataBind();
                    dtgAFExit.DataBind();
                    dtgAFExitOther.DataBind();
                    dtgHRExit.DataBind();
                    dtgHRTrainingExit.DataBind();

                    ReplaceExtNoAndUser();

                    #region  Ẩn các lưới không có dữ liệu
                    if (dtgIT.Items.Count <= 0)
                        pnlIT.Visible = false;
                    else
                        pnlIT.Visible = true;

                    if (dtgITOther.Items.Count <= 0)
                        pnlITOther.Visible = false;
                    else
                        pnlITOther.Visible = true;

                    if (dtgExitList.Items.Count <= 0)
                        pnlExitList.Visible = false;
                    else
                        pnlExitList.Visible = true;

                    if (dtgHROtherExit.Items.Count <= 0)
                        pnlHROtherExit.Visible = false;
                    else
                        pnlHROtherExit.Visible = true;

                    if (dtgFAExit.Items.Count <= 0)
                        pnlFAExit.Visible = false;
                    else
                        pnlFAExit.Visible = true;

                    if (dtgFAExitOther.Items.Count <= 0 || CheckFAOther==false)
                        pnlFAExitOther.Visible = false;
                    else
                        pnlFAExitOther.Visible = true;

                    if (dtgAFExit.Items.Count <= 0)
                        pnlAFExit.Visible = false;
                    else
                        pnlAFExit.Visible = true;

                    if (dtgAFExitOther.Items.Count <= 0)
                        pnlAFExitOther.Visible = false;
                    else
                        pnlAFExitOther.Visible = true;

                    if (dtgHRExit.Items.Count <= 0)
                        pnlHRExit.Visible = false;
                    else
                        pnlHRExit.Visible = true;

                    if (dtgHRTrainingExit.Items.Count <= 0)
                        pnlHRTrainingExit.Visible = false;
                    else
                        pnlHRTrainingExit.Visible = true;
                    #endregion

                    
                }
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
        }

        protected string GetApprover(List<ExitTypeApproveDto> list, int ExitType, bool property)
        {
            string result = string.Empty;
            result = list.Where(n => n.ExitTypeID == ExitType && n.Property == property).Single().Approver;

            return result;
        }

        protected string GetExtNo(List<ExitTypeApproveDto> list, string approver)
        {
            string result = string.Empty;
            if (list.Where(n => n.Approver == approver).Count() > 0)
            {
                result = list.Where(n => n.Approver == approver).First().ExtNo;
            }

            if (string.IsNullOrEmpty(result) || result == "") return "";

            return " (Ext." + result + ")";
        }

        protected void ReplaceExtNoAndUser()
        {
            var approver = GetApprover(listApprove, (int)ExitType.AF, false);
            lblAFExit.Text = lblAFExit.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.AFOther, true);
            lblAFExitOther.Text = lblAFExitOther.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.FA, true);
            lblFAExit.Text = lblFAExit.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.FAOther, true);
            lblFAExitOther.Text = lblFAExitOther.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.IT, false);
            lblIT.Text = lblIT.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.ITOther, true);
            lblITOther.Text = lblITOther.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.HR, false);
            lblHRExit.Text = lblHRExit.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.HROther, true);
            lblHROtherExit.Text = lblHROtherExit.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));

            approver = GetApprover(listApprove, (int)ExitType.HRTraining, true);
            lblHRTrainingExit.Text = lblHRTrainingExit.Text.Replace("$Value$", approver + GetExtNo(listApprove, approver));
        }
        /// <summary>
        /// xứ lý sự kiện khi nhấn nút phê duyệt
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnTopApprove_Click(object sender, EventArgs e)
        {
            HR_WorkingRecordBL bl_wr = new HR_WorkingRecordBL();
            HR_WorkingRecordCollection cl_wr = new HR_WorkingRecordCollection();
            TER_ExitFormCollection col_exit = new TER_ExitFormCollection();
            K2Collection colK2 = new K2Collection();
            K2CoreLib.K2Utils cl_util = new K2CoreLib.K2Utils();
            vHCS.SYS.Business.SYS_MailTemplateCollection col_mail = new SYS.Business.SYS_MailTemplateCollection();
            HIE_EmpInHierarchyCollection cl_emphie = new HIE_EmpInHierarchyCollection();
            TER_ExitFormCollection cl_exit=new TER_ExitFormCollection();
            try
            {
                
                bool check = col_exit.CheckSendApprove(EmpHeader.EmployeeId);
                if (check == false)
                {
                    BusinessLogicCustomException ex = new BusinessLogicCustomException("12617");
                    throw ex;
                }

                #region Xử lý K2
                //Start K2

                string empid = EmpHeader.EmployeeId.ToString();
                int CountApprove = 0;
                string Requester=SessionManager.UserDomain;
                string DeptHead="";
                string AFProperty="";
                string ITProperty="";
                string Accounter="";
                string HRProperty="";
                string HRTraining="";
                string HROther="";
                string AFOther = "";
                string ITOther = "";
                string AccounterOther = "";

                string MailDeptHead = "";
                string MailAFProperty = "";
                string MailITProperty = "";
                string MailAccounter = "";
                string MailHRProperty = "";
                string MailHRTraining = "";
                string MailHROther = "";
                string MailAFOther = "";
                string MailITOther = "";
                string MailAccounterOther = "";

                #region Lấy thông tin người phê duyệt
                if (dtgExitList.Items.Count > 0)
                {
                    //Lấy thông tin phê duyệt Dept Head
                    //DeptHead = cl_exit.GetUserApprove(1, EmpHeader.EmployeeId, true);
                    EmpDataProvider data = new EmpDataProvider();
                    DeptHead = data.GetUserManager(EmpHeader.EmployeeId.ToString()).DeptHead;
                    MailDeptHead = col_mail.ReturnEmail(DeptHead);
                    CountApprove += 1;
                }
                if (dtgAFExit.Items.Count > 0)
                {
                    //Lấy thông tin phê duyệt tài sản AF
                    //AFProperty = cl_exit.GetUserApprove(2, EmpHeader.EmployeeId, false);
                    AFProperty = GetApprover(listApprove, (int)ExitType.AF, false); 
                    MailAFProperty = col_mail.ReturnEmail(AFProperty);
                    CountApprove += 1;
                }
                if (dtgIT.Items.Count > 0)
                {
                    //Lấy thông tin phê duyệt tài sản IT
                    //ITProperty = cl_exit.GetUserApprove(1, EmpHeader.EmployeeId, false);
                    ITProperty = GetApprover(listApprove, (int)ExitType.IT, false); 
                    MailITProperty = col_mail.ReturnEmail(ITProperty);
                    CountApprove += 1;
                }
                if (dtgFAExit.Items.Count > 0)
                {
                    //Lấy thông tin phê duyệt tài chính, kế toán
                    //Accounter = cl_exit.GetUserApprove(4, EmpHeader.EmployeeId, true);
                    Accounter = GetApprover(listApprove, (int)ExitType.FA, true);
                    MailAccounter = col_mail.ReturnEmail(Accounter);
                    CountApprove += 1;
                }
                if (dtgHRExit.Items.Count > 0)
                {
                    //Lấy thông tin phê duyệt tài sản HR
                    //HRProperty = cl_exit.GetUserApprove(3, EmpHeader.EmployeeId, false);
                    HRProperty = GetApprover(listApprove, (int)ExitType.HR, false);
                    MailHRProperty = col_mail.ReturnEmail(HRProperty);
                    CountApprove += 1;
                }
                if (dtgHRTrainingExit.Items.Count > 0)
                {
                    //Lấy thông tin phê duyệt phòng nhân sự (tranining)
                    //HRTraining = cl_exit.GetUserApprove(10, EmpHeader.EmployeeId, true);
                    HRTraining = GetApprover(listApprove, (int)ExitType.HRTraining, true);
                    MailHRTraining = col_mail.ReturnEmail(HRTraining);
                    CountApprove += 1;
                }
                if (dtgHROtherExit.Items.Count > 0)
                {
                    //Lấy thông tin phê duyệt phòng nhân sự (other)
                    //HROther = cl_exit.GetUserApprove(8, EmpHeader.EmployeeId, true);
                    HROther = GetApprover(listApprove, (int)ExitType.HROther, true);
                    MailHROther = col_mail.ReturnEmail(HROther);
                    CountApprove += 1;
                }
                if (dtgAFExitOther.Items.Count > 0)
                {
                    //Lấy thông tin phê duyệt phòng hành chánh (other)
                    //AFOther = cl_exit.GetUserApprove(11, EmpHeader.EmployeeId, true);
                    AFOther = GetApprover(listApprove, (int)ExitType.AFOther, true);
                    MailAFOther = col_mail.ReturnEmail(AFOther);
                    CountApprove += 1;
                }
                if (dtgITOther.Items.Count > 0)
                {
                    //Lấy thông tin phê duyệt phòng IT (other)
                    //ITOther = cl_exit.GetUserApprove(12, EmpHeader.EmployeeId, true);
                    ITOther = GetApprover(listApprove, (int)ExitType.ITOther, true);
                    MailITOther = col_mail.ReturnEmail(ITOther);
                    CountApprove += 1;
                }
                if (dtgFAExitOther.Items.Count > 0 && pnlFAExitOther.Visible==true)
                {
                    //Lấy thông tin phê duyệt phòng FA (other)
                    //AccounterOther = cl_exit.GetUserApprove(13, EmpHeader.EmployeeId, true);
                    AccounterOther = GetApprover(listApprove, (int)ExitType.FAOther, true);
                    MailAccounterOther = col_mail.ReturnEmail(AccounterOther);
                    CountApprove += 1;
                }
                #endregion


                int iTypeID = K2CoreLib.K2Utils.GetTypeIDProcessFromEfrom("HR_ExitCheckList");
                string sLinkContent = ConfigurationManager.AppSettings.Get("History_ExitCheckList");
                string status = "0"; //(0: In process, 1: Complete, 2: Rework, 3:Reject)
                string eformid = K2CoreLib.K2Utils.submit2EForm(SessionManager.UserDomain, SessionManager.UserDomain, iTypeID, DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"), sLinkContent, status, EmpHeader.Username);
                string[] lst_eformid = eformid.Split(',');

                #region Xử lý gửi mail cho người approver
                SYS_MailTemplateDTO _mailApprover = new SYS_MailTemplateDTO();
                _mailApprover = col_mail.GetMailContentWithSignature("K2-Waiting-NoApproveByMail", "K2-Signature");

                string MailContent_NextApprover = _mailApprover.ContentVN;//col_mail.ReturnMailContent("ExitCheckList_NextApprover");
                string MailSubject_NextApprover = _mailApprover.TitleVN;

                //Gán thông tin cho mail subject
                MailSubject_NextApprover = MailSubject_NextApprover.Replace("$TicketName$", _TicketName__);
                MailSubject_NextApprover = MailSubject_NextApprover.Replace("$Code$", lst_eformid[0]);
                MailSubject_NextApprover = MailSubject_NextApprover.Replace("$Creator$", SessionManager.UserDomain);
                //Gán thông tin Mail content
                MailContent_NextApprover = MailContent_NextApprover.Replace("$TicketName$", _TicketName__);
                MailContent_NextApprover = MailContent_NextApprover.Replace("$Creator$", SessionManager.UserDomain);
                MailContent_NextApprover = MailContent_NextApprover.Replace("$ContentTicket$", "");
                MailContent_NextApprover = MailContent_NextApprover.Replace("$url$", ConfigurationManager.AppSettings.Get("ExitCheckListApprove_Old_Old"));
                
                #endregion


                #region Xử lý gửi mail cho người requester
                SYS_MailTemplateDTO _mailRequester = new SYS_MailTemplateDTO();
                _mailRequester = col_mail.GetMailContentWithSignature("K2-Submit", "K2-Signature");

                string MailContent_Request = _mailRequester.ContentVN; //col_mail.ReturnMailContent("ExitCheckList_Creator");
                string MailSubject_Request = _mailRequester.TitleVN;
                string MailRequester = "";
                string MailHRBP = "";

                //Gán thông tin vào content mail title
                MailSubject_Request = MailSubject_Request.Replace("$TicketName$", _TicketName__);
                MailSubject_Request = MailSubject_Request.Replace("$Code$", lst_eformid[0]);

                //Gán thông tin vào content mail requester
                MailContent_Request = MailContent_Request.Replace("$TicketName$", _TicketName__);
                MailContent_Request = MailContent_Request.Replace("$Approver$", "his/her Department Head, IT, AF, FA, HR");
                MailContent_Request = MailContent_Request.Replace("$url$", ConfigurationManager.AppSettings.Get("ExitCheckListHis") + EmpHeader.EmployeeId.ToString() + "&ProcessID=" + lst_eformid[1]);
                MailContent_Request = MailContent_Request.Replace("$ContentTicket$", "");

                //Lấy thông tin địa chỉ mail của người requester
                MailRequester = col_mail.ReturnEmail(SessionManager.UserDomain);
                MailHRBP = col_mail.ReturnEmail(colK2.GetUserOfHRP(EmpHeader.EmployeeId));
                #endregion

                //Get expire
                string ExpireDay = ConfigurationManager.AppSettings.Get("EXIT_ExpireDay");
                string ExpireAlert = ConfigurationManager.AppSettings.Get("EXIT_ExpireAlert");
                string ApproveLink = ConfigurationManager.AppSettings.Get("ExitCheckListApprove");
                string ViewHistoryLink = ConfigurationManager.AppSettings.Get("ExitCheckListHis") + EmpHeader.EmployeeId.ToString() + "&ProcessID=" + lst_eformid[1];

                int proid = K2CoreLib.K2Utils.StartExitCheckList(EmpHeader.EmployeeId.ToString(), Requester, DeptHead, Accounter, HROther, AFProperty, HRProperty, ITProperty, HRTraining, CountApprove.ToString(),lst_eformid[1],MailRequester
                    , MailContent_Request,MailSubject_Request,MailDeptHead,MailAccounter,MailHROther,MailAFProperty,MailHRProperty,MailITProperty,MailHRTraining,MailContent_NextApprover,MailSubject_NextApprover,txtEmail.Text.Trim()
                    , AFOther, ITOther, AccounterOther, MailAFOther, MailITOther, MailAccounterOther, lst_eformid[0], MailHRBP,"",EmpHeader.Username
                    ,ApproveLink,ViewHistoryLink,ExpireDay,ExpireAlert);

                //Cập nhật lại view flow trên eform
                K2CoreLib.K2Utils.Update2EForm("0", lst_eformid[1], proid.ToString());
                //Cập nhật lại linkcontent trên eform
                K2CoreLib.K2Utils.Update2EForm_LinkContent(lst_eformid[1], ConfigurationManager.AppSettings.Get("History_ExitCheckList")+EmpHeader.EmployeeId.ToString()+  "&ProcessID=" + lst_eformid[1]);

                if (proid >= 0)
                {
                    Save(lst_eformid[1]);
                }
                ClientScript.RegisterStartupScript(Page.GetType(), "ClosePage", "ClosePage();", true);

                #region begin to save history
                cl_wr.InsertHistory(Convert.ToInt64(lst_eformid[1]), DateTime.Now, SessionManager.UserDomain, "Requester", "Submitted", "", 5,0);
                #endregion End save

                #endregion
            }
            catch (Exception ex)
            {
                AlertMessage mess = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(mess);
            }
        }

        public void Save(string processid)
        {
            try
            {
                if (this.EmpHeader.EmployeeId > 0)
                {

                    LS_ExitFormCollection clExit = new LS_ExitFormCollection();
                    HR_EmpCVCollection cl_emp = new HR_EmpCVCollection();
                    long lngCheck = clExit.CheckApprove(EmpHeader.EmployeeId);
                    TER_ExitFormCollection clDate = new TER_ExitFormCollection();
                    DataTable dat = new DataTable();
                    dat = clDate.SelectByEmpId(this.EmpHeader.EmployeeId);
                    TER_ExitFormCollection cl = new TER_ExitFormCollection();
                    List<HR_PropertyBL> lstProperty = new List<HR_PropertyBL>();
                    HR_PropertyCollection clproperty = new HR_PropertyCollection();
                    List<TER_ExitFormDTO> lstExit = new List<TER_ExitFormDTO>();
                    //lấy thông tin ở mục nhân sự (khác)
                    foreach (GridDataItem dataItem in dtgHROtherExit.Items)
                    {
                        TER_ExitFormDTO dto = new TER_ExitFormDTO();
                        //dto.Account = ((RadNumericTextBox)dataItem["ExitID"].FindControl("txtFinalPaymentHROther")).Text;
                        //dto.FromDate = Convert.ToDateTime(((RadDatePicker)dataItem["ExitID"].FindControl("txtTranferDateHROther")).SelectedDate);
                        //dto.Comment = ((RadTextBox)dataItem["ExitID"].FindControl("txtCommentsOther")).Text;
                        dto.LSExitFormID = Convert.ToInt64(dataItem["ExitID"].Text);
                        dto.CreatedUser = SessionManager.UserDomain;
                        dto.CreatedDate = DateTime.Now;
                        dto.EmpID = this.EmpHeader.EmployeeId;
                        dto.Processid = processid;
                        lstExit.Add(dto);
                    }
                    //lấy thông tin ở mục nhân sự (đào tạo)
                    foreach (GridDataItem dataItem in dtgHRTrainingExit.Items)
                    {
                        TER_ExitFormDTO dto = new TER_ExitFormDTO();
                        //dto.Account = ((RadNumericTextBox)dataItem["ExitID"].FindControl("txtFinalPaymentHROther")).Text;
                        //dto.FromDate = Convert.ToDateTime(((RadDatePicker)dataItem["ExitID"].FindControl("txtTranferDateHROther")).SelectedDate);
                        //dto.Comment = ((RadTextBox)dataItem["ExitID"].FindControl("txtCommentsOther")).Text;
                        dto.LSExitFormID = Convert.ToInt64(dataItem["ExitID"].Text);
                        dto.CreatedUser = SessionManager.UserDomain;
                        dto.CreatedDate = DateTime.Now;
                        dto.EmpID = this.EmpHeader.EmployeeId;
                        dto.Processid = processid;
                        lstExit.Add(dto);

                    }
                    //Lấy thông tin ở mục phòng tài chính & kế toán
                    foreach (GridDataItem dataItem in dtgFAExit.Items)
                    {

                        TER_ExitFormDTO dto = new TER_ExitFormDTO();
                        //dto.Account = ((RadNumericTextBox)dataItem["ExitID"].FindControl("txtFinalPaymentFA")).Text;
                        //dto.FromDate = Convert.ToDateTime(((RadDatePicker)dataItem["ExitID"].FindControl("txtTranferDateFA")).SelectedDate);
                        //dto.Comment = ((RadTextBox)dataItem["ExitID"].FindControl("txtCommentsFA")).Text;
                        dto.LSExitFormID = Convert.ToInt64(dataItem["ExitID"].Text);
                        dto.CreatedUser = SessionManager.UserDomain;
                        dto.CreatedDate = DateTime.Now;
                        dto.EmpID = this.EmpHeader.EmployeeId;
                        dto.Processid = processid;
                        lstExit.Add(dto);
                    }
                    
                    //Lấy thông tin ở mục tài chính kế toán other
                    bool CheckFAOther = Convert.ToBoolean(hdfCheckFAOther.Value);
                    if (CheckFAOther)
                    {
                        foreach (GridDataItem dataItem in dtgFAExitOther.Items)
                        {
                            TER_ExitFormDTO dto = new TER_ExitFormDTO();
                            //dto.Account = ((RadNumericTextBox)dataItem["ExitID"].FindControl("txtFinalPaymentFA")).Text;
                            //dto.FromDate = Convert.ToDateTime(((RadDatePicker)dataItem["ExitID"].FindControl("txtTranferDateFA")).SelectedDate);
                            //dto.Comment = ((RadTextBox)dataItem["ExitID"].FindControl("txtCommentsFA")).Text;
                            dto.LSExitFormID = Convert.ToInt64(dataItem["ExitID"].Text);
                            dto.CreatedUser = SessionManager.UserDomain;
                            dto.CreatedDate = DateTime.Now;
                            dto.EmpID = this.EmpHeader.EmployeeId;
                            dto.Processid = processid;
                            lstExit.Add(dto);
                        }
                    }

                    //Lấy thông tin ở mục phòng công nghệ thông tin other
                    foreach (GridDataItem dataItem in dtgITOther.Items)
                    {
                        TER_ExitFormDTO dto = new TER_ExitFormDTO();
                        //dto.Account = ((RadNumericTextBox)dataItem["ExitID"].FindControl("txtFinalPaymentFA")).Text;
                        //dto.FromDate = Convert.ToDateTime(((RadDatePicker)dataItem["ExitID"].FindControl("txtTranferDateFA")).SelectedDate);
                        //dto.Comment = ((RadTextBox)dataItem["ExitID"].FindControl("txtCommentsFA")).Text;
                        dto.LSExitFormID = Convert.ToInt64(dataItem["ExitID"].Text);
                        dto.CreatedUser = SessionManager.UserDomain;
                        dto.CreatedDate = DateTime.Now;
                        dto.EmpID = this.EmpHeader.EmployeeId;
                        dto.Processid = processid;
                        lstExit.Add(dto);
                    }

                    //Lấy thông tin ở mục phòng hành chánh other
                    foreach (GridDataItem dataItem in dtgAFExitOther.Items)
                    {

                        TER_ExitFormDTO dto = new TER_ExitFormDTO();
                        //dto.Account = ((RadNumericTextBox)dataItem["ExitID"].FindControl("txtFinalPaymentFA")).Text;
                        //dto.FromDate = Convert.ToDateTime(((RadDatePicker)dataItem["ExitID"].FindControl("txtTranferDateFA")).SelectedDate);
                        //dto.Comment = ((RadTextBox)dataItem["ExitID"].FindControl("txtCommentsFA")).Text;
                        dto.LSExitFormID = Convert.ToInt64(dataItem["ExitID"].Text);
                        dto.CreatedUser = SessionManager.UserDomain;
                        dto.CreatedDate = DateTime.Now;
                        dto.EmpID = this.EmpHeader.EmployeeId;
                        dto.Processid = processid;
                        lstExit.Add(dto);
                    }
                    
                    //lấy thông tin ở mục thông tin phòng ban
                    foreach (GridDataItem dataItem in dtgExitList.Items)
                    {
                        TER_ExitFormDTO dto = new TER_ExitFormDTO();
                        //dto.Account = ((RadNumericTextBox)dataItem["ExitID"].FindControl("txtFinalPaymentDM")).Text;
                        //dto.FromDate = Convert.ToDateTime(((RadDatePicker)dataItem["ExitID"].FindControl("txtTranferDateDM")).SelectedDate);
                        //dto.Comment = ((RadTextBox)dataItem["ExitID"].FindControl("txtCommentsDM")).Text;
                        dto.LSExitFormID = Convert.ToInt64(dataItem["ExitID"].Text);
                        dto.CreatedUser = SessionManager.UserDomain;
                        dto.CreatedDate = DateTime.Now;
                        dto.EmpID = this.EmpHeader.EmployeeId;
                        dto.Processid = processid;
                        lstExit.Add(dto);
                    }

                    cl.AddNew(lstExit,radBenifit_Y.Checked?true:false,radConsult_Y.Checked?true:false);
                    cl_emp.UpdateEmail(EmpHeader.EmployeeId, txtEmail.Text.Trim());

                    //lấy thông tin ở mục phòng hành chính
                    foreach (GridDataItem dataItem in dtgAFExit.Items)
                    {
                        HR_PropertyBL bl = new HR_PropertyBL();
                        RadDatePicker datetime = (RadDatePicker)dataItem.FindControl("txtRecoveryDate");
                        if (datetime.SelectedDate > datetime.MinDate)
                        {
                            bl.PropertyID = Convert.ToInt64(dataItem["ExitID"].Text);
                            bl.RecoveryDate =Convert.ToDateTime(datetime.SelectedDate);
                            lstProperty.Add(bl);
                        }
                    }

                    //lấy thông tin ở mục phòng công nghệ thông tin 
                    foreach (GridDataItem dataItem in dtgIT.Items)
                    {
                        HR_PropertyBL bl = new HR_PropertyBL();
                        RadDatePicker datetime = (RadDatePicker)dataItem.FindControl("txtRecoveryDate");
                        if (datetime.SelectedDate > datetime.MinDate)
                        {
                            bl.PropertyID = Convert.ToInt64(dataItem["ExitID"].Text);
                            bl.RecoveryDate = Convert.ToDateTime(datetime.SelectedDate);
                            lstProperty.Add(bl);
                        }
                    }

                    //lấy thông tin ở mục phòng nhân sự
                    foreach (GridDataItem dataItem in dtgHRExit.Items)
                    {
                        HR_PropertyBL bl = new HR_PropertyBL();
                        RadDatePicker datetime = (RadDatePicker)dataItem.FindControl("txtRecoveryDate");
                        if (datetime.SelectedDate > datetime.MinDate)
                        {
                            bl.PropertyID = Convert.ToInt64(dataItem["ExitID"].Text);
                            bl.RecoveryDate = Convert.ToDateTime(datetime.SelectedDate);
                            lstProperty.Add(bl);
                        }
                    }

                    clproperty.UpdateRecoveryDate(lstProperty);
                }
                else
                {
                    MessageBox.ShowByMessCode("10095", MessageType.Error);
                }
            }
            catch (Exception ex)
            {
                AlertMessage mess = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(mess);
            }
        }

        #region Export data follow template
        public string ExportExcel(string sPathFile, DataTable dtData, DataTable dtData_AF, DataTable dtData_IT, DataTable dtData_FA, DataTable dtData_HR, DataTable dtData_Other, DataTable dtReplace,DataTable dtAFOther,DataTable dtITOther,DataTable dtFAOther,DataTable dtHRTraining)
        {
            try
            {
                Workbook wb = new Workbook();
                wb.Open(sPathFile);

                #region Cau hinh tren worksheet2
                Worksheet ws_config = wb.Worksheets[1];
                //--Khai bao tham so cau hinh
                int iMax_Colum = Convert.ToInt16(ws_config.Cells["H4"].Value);
                int iRow_Group1 = Convert.ToInt16(ws_config.Cells["B4"].Value) - 1;
                int iRow_Group2 = Convert.ToInt16(ws_config.Cells["B5"].Value) - 1;
                int iRow_Detail = Convert.ToInt16(ws_config.Cells["B6"].Value) - 1;
                //begin
                int iRow_Detail_AF = Convert.ToInt16(ws_config.Cells["B9"].Value) - 1;
                int iRow_Detail_AFOther = Convert.ToInt16(ws_config.Cells["B10"].Value) - 1;
                int iRow_Detail_IT = Convert.ToInt16(ws_config.Cells["B11"].Value) - 1;
                int iRow_Detail_ITOther = Convert.ToInt16(ws_config.Cells["B12"].Value) - 1;
                int iRow_Detail_FA = Convert.ToInt16(ws_config.Cells["B13"].Value) - 1;
                int iRow_Detail_FAOther = Convert.ToInt16(ws_config.Cells["B14"].Value) - 1;
                int iRow_Detail_HR = Convert.ToInt16(ws_config.Cells["B15"].Value) - 1;
                int iRow_Detail_HRTraining = Convert.ToInt16(ws_config.Cells["B16"].Value) - 1;
                int iRow_Detail_Other = Convert.ToInt16(ws_config.Cells["B17"].Value) - 1;

                //end
                int iRow_End = Convert.ToInt16(ws_config.Cells["B7"].Value) - 1;

                string sNo = ws_config.Cells["B8"].Value.ToString();
                string sGroup1Active = ws_config.Cells["D4"].Value.ToString();
                string sGroup2Active = ws_config.Cells["D5"].Value.ToString();
                string sRowEndActive = ws_config.Cells["D7"].Value.ToString();

                string sGroup1Column = ws_config.Cells["E4"].Value == null ? "" : ws_config.Cells["E4"].Value.ToString();
                string sGroup2Column = ws_config.Cells["E5"].Value == null ? "" : ws_config.Cells["E5"].Value.ToString();

                string sVersionExcel = ws_config.Cells["H5"].Value == null ? "2003" : ws_config.Cells["H5"].Value.ToString();
                string sFileNameExcel = ws_config.Cells["H6"].Value == null ? "" : ws_config.Cells["H6"].Value.ToString();
                string sPassword = ws_config.Cells["H7"].Value == null ? "" : ws_config.Cells["H7"].Value.ToString();
                #endregion

                Worksheet ws = wb.Worksheets[0];

                #region Lay vi tri cac tieu de
                //databind phòng Manager
                ArrayList arr = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol = new ArrayList();
                ArrayList arrIndexFormular = new ArrayList();
                int IndexRow = 0;
                bool bIsSTT = false; //false: khong co Cot STT; //true: co Cot STT

                for (int i = 0; i < dtData.Columns.Count; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr.Add(sSearch);
                            arrIndexCol.Add(cTmp.Column);
                            IndexRow = cTmp.Row;
                        }
                    }

                    if (sSearch.ToUpper() == "NO")
                        bIsSTT = true;
                }
                #endregion

                #region lấy vị trí tiêu đề :AF
                //begin
                ArrayList arr_add = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_add = new ArrayList();
                ArrayList arrIndexFormular_add = new ArrayList();
                int IndexRow_add = 0;
                bool bIsSTT_add = false; //false: khong co Cot STT; //true: co Cot STT
                //end

                //begin
                for (int i = 0; i < dtData_AF.Columns.Count; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_AF, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_add.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_add.Add(sSearch);
                            arrIndexCol_add.Add(cTmp.Column);
                            IndexRow_add = cTmp.Row;
                        }
                    }

                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_add = true;
                }
                //end
                #endregion

                #region tiêu đề AFOther
                //databind phòng Manager
                ArrayList arr_AFOther = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_AFOther = new ArrayList();
                ArrayList arrIndexFormular_AFOther = new ArrayList();
                int IndexRow_AFOther = 0;
                bool bIsSTT_AFOther = false; //false: khong co Cot STT; //true: co Cot STT

                for (int i = 0; i < dtAFOther.Columns.Count; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_AFOther, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_AFOther.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_AFOther.Add(sSearch);
                            arrIndexCol_AFOther.Add(cTmp.Column);
                            IndexRow_AFOther = cTmp.Row;
                        }
                    }

                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_AFOther = true;
                }
                #endregion

                #region Lấy vị trí tiêu đề :IT
                //begin
                ArrayList arr_IT = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_IT = new ArrayList();
                ArrayList arrIndexFormular_IT = new ArrayList();
                int IndexRow_IT = 0;
                bool bIsSTT_IT = false; //false: khong co Cot STT; //true: co Cot STT
                //end

                //begin
                for (int i = 0; i < dtData_IT.Columns.Count; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_IT, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_IT.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_IT.Add(sSearch);
                            arrIndexCol_IT.Add(cTmp.Column);
                            IndexRow_IT = cTmp.Row;
                        }
                    }

                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_IT = true;
                }
                //end
                #endregion

                #region Lấy vị trí tiêu đề :IT Other
                //begin
                ArrayList arr_ITOther = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_ITOther = new ArrayList();
                ArrayList arrIndexFormular_ITOther = new ArrayList();
                int IndexRow_ITOther = 0;
                bool bIsSTT_ITOther = false; //false: khong co Cot STT; //true: co Cot STT
                //end

                //begin
                for (int i = 0; i < dtITOther.Columns.Count; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_ITOther, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_ITOther.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_ITOther.Add(sSearch);
                            arrIndexCol_ITOther.Add(cTmp.Column);
                            IndexRow_ITOther = cTmp.Row;
                        }
                    }

                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_ITOther = true;
                }
                //end
                #endregion

                #region lấy vị trí tiêu đề :FA
                //databind phòng Manager
                ArrayList arr_FA = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_FA = new ArrayList();
                ArrayList arrIndexFormular_FA = new ArrayList();
                int IndexRow_FA = 0;
                bool bIsSTT_FA = false; //false: khong co Cot STT; //true: co Cot STT
                for (int i = 0; i < dtData_FA.Columns.Count; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_FA, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_FA.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_FA.Add(sSearch);
                            arrIndexCol_FA.Add(cTmp.Column);
                            IndexRow_FA = cTmp.Row;
                        }
                    }
                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_FA = true;
                }
                #endregion

                #region lấy vị trí tiêu đề :FA Other
                //databind phòng Manager
                ArrayList arr_FAOther = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_FAOther = new ArrayList();
                ArrayList arrIndexFormular_FAOther = new ArrayList();
                int IndexRow_FAOther = 0;
                bool bIsSTT_FAOther = false; //false: khong co Cot STT; //true: co Cot STT
                for (int i = 0; i < dtFAOther.Columns.Count; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_FAOther, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_FAOther.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_FAOther.Add(sSearch);
                            arrIndexCol_FAOther.Add(cTmp.Column);
                            IndexRow_FAOther = cTmp.Row;
                        }
                    }
                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_FAOther = true;
                }
                #endregion

                #region Lấy vị trí tiêu đề :HR
                //begin
                ArrayList arr_HR = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_HR = new ArrayList();
                ArrayList arrIndexFormular_HR = new ArrayList();
                int IndexRow_HR = 0;
                bool bIsSTT_HR = false; //false: khong co Cot STT; //true: co Cot STT
                //end

                //begin
                for (int i = 0; i < dtData_HR.Columns.Count; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_HR, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_HR.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_HR.Add(sSearch);
                            arrIndexCol_HR.Add(cTmp.Column);
                            IndexRow_HR = cTmp.Row;
                        }
                    }

                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_HR = true;
                }
                //end
                #endregion

                #region Lấy vị trí tiêu đề :HR Training
                //begin
                ArrayList arr_HRTraining = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_HRTraining = new ArrayList();
                ArrayList arrIndexFormular_HRTraining = new ArrayList();
                int IndexRow_HRTraining = 0;
                bool bIsSTT_HRTraining = false; //false: khong co Cot STT; //true: co Cot STT
                //end

                //begin
                for (int i = 0; i < dtHRTraining.Columns.Count; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_HRTraining, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_HRTraining.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_HRTraining.Add(sSearch);
                            arrIndexCol_HRTraining.Add(cTmp.Column);
                            IndexRow_HRTraining = cTmp.Row;
                        }
                    }

                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_HRTraining = true;
                }
                //end
                #endregion

                #region lấy vị trí tiêu đề :Other
                //databind phòng Manager
                ArrayList arr_Other = new ArrayList(); //Mang chua cot detail
                ArrayList arrIndexCol_Other = new ArrayList();
                ArrayList arrIndexFormular_Other = new ArrayList();
                int IndexRow_Other = 0;
                bool bIsSTT_Other = false; //false: khong co Cot STT; //true: co Cot STT
                for (int i = 0; i < dtData_Other.Columns.Count; i++)
                {
                    Cell cTmp = ws.Cells[iRow_Detail_Other, i];
                    string sSearch = cTmp.Value == null ? "" : cTmp.Value.ToString().Trim();

                    if (cTmp.IsFormula == true)
                        arrIndexFormular_Other.Add(cTmp.Column);

                    else
                    {
                        if (sSearch != "" && sSearch.ToUpper() != "NO")
                        {
                            arr_Other.Add(sSearch);
                            arrIndexCol_Other.Add(cTmp.Column);
                            IndexRow_Other = cTmp.Row;
                        }
                    }
                    if (sSearch.ToUpper() == "NO")
                        bIsSTT_Other = true;
                }
                #endregion

                int irow = 0;

                if (dtData != null)
                {
                    if (dtData.Rows.Count > 0)
                    {
                        #region insert cac detail
                        for (int i = 0; i < arr.Count; i++)
                        {
                            if (i == 0)
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(dtData, false, IndexRow, Convert.ToInt16(arrIndexCol[i]), arr[i].ToString(), true);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow, Convert.ToInt16(arrIndexCol[i])].PutValue(null);
                                }
                            }
                            else
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(dtData, false, IndexRow, Convert.ToInt16(arrIndexCol[i]), arr[i].ToString(), false);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow, Convert.ToInt16(arrIndexCol[i])].PutValue(null);
                                }
                            }
                        }

                        //--Sao chep cong thuc

                        for (int i = 0; i < arrIndexFormular.Count; i++)
                        {
                            Cell sTmp = ws.Cells[iRow_Detail, Convert.ToInt16(arrIndexFormular[i])];

                            for (int j = iRow_Detail + 1; j < iRow_Detail + dtData.Rows.Count; j++)
                            {
                                ws.Cells[j, Convert.ToInt16(arrIndexFormular[i])].Copy(sTmp);
                            }
                        }
                        //--End
                        ws.Cells.DeleteRow(IndexRow + dtData.Rows.Count);
                        #endregion
                    }
                    else
                    {
                        ws.Cells.DeleteRow(iRow_Detail);
                        irow += 1;
                    }
                }

                int rowAF = 0;
                if (dtData_AF.Rows.Count > 0)
                {
                    rowAF = this.ExportDMProperty(iRow_Detail_AF, dtData.Rows.Count, dtData_AF, iMax_Colum, ws, arr_add, arrIndexCol_add, arrIndexFormular_add, IndexRow_add);
                }
                else
                {
                    rowAF = this.ExportDMProperty(iRow_Detail_AF , dtData.Rows.Count, dtData_AF, iMax_Colum, ws, arr_add, arrIndexCol_add, arrIndexFormular_add, IndexRow_add);
                    irow += 1;
                    
                    //if (dtData.Rows.Count <= 0)
                    //{
                    //    rowAF = this.ExportDMProperty(iRow_Detail_AF, dtData.Rows.Count, dtData_AF, iMax_Colum, ws, arr_add, arrIndexCol_add, arrIndexFormular_add, IndexRow_add);
                    //}
                    //else
                    //    rowAF = this.ExportDMProperty(iRow_Detail_AF + 1, dtData.Rows.Count, dtData_AF, iMax_Colum, ws, arr_add, arrIndexCol_add, arrIndexFormular_add, IndexRow_add);
                }

                int rowAF_Other = 0;
                if (dtAFOther.Rows.Count > 0)
                {
                    rowAF_Other = this.ExportDMProperty(iRow_Detail_AFOther, (dtData.Rows.Count - 2) + rowAF, dtAFOther, iMax_Colum, ws, arr_AFOther, arrIndexCol_AFOther, arrIndexFormular_AFOther, IndexRow_AFOther);
                }
                else
                {
                    //ws.Cells.Rows[
                    irow += 1;
                    //if (dtData.Rows.Count <= 0)
                    //{
                    //    rowAF_Other = this.ExportDMProperty(iRow_Detail_AF, dtData.Rows.Count, dtData_AF, iMax_Colum, ws, arr_add, arrIndexCol_add, arrIndexFormular_add, IndexRow_add);
                    //}
                    //else
                    //    rowAF_Other = this.ExportDMProperty(iRow_Detail_AF + 1, dtData.Rows.Count, dtData_AF, iMax_Colum, ws, arr_add, arrIndexCol_add, arrIndexFormular_add, IndexRow_add);
                }

                int rowIT = 0;
                if (dtData_IT.Rows.Count > 0)
                {
                    rowIT = this.ExportDMProperty(iRow_Detail_IT, rowAF+rowAF_Other + dtData.Rows.Count-1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                }
                else
                {
                    rowIT = this.ExportDMProperty(iRow_Detail_IT , rowAF + rowAF_Other + dtData.Rows.Count-1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                    irow += 1;
                    //if (rowAF <= 0 && dtData.Rows.Count <= 0)
                    //{
                    //    rowIT = this.ExportDMProperty(iRow_Detail_IT - 1, rowAF + dtData.Rows.Count - 1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                    //}
                    //else if ((rowAF <= 0 && dtData.Rows.Count > 0) || rowAF > 0 && dtData.Rows.Count <= 0)
                    //{
                    //    rowIT = this.ExportDMProperty(iRow_Detail_IT, rowAF + dtData.Rows.Count - 1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                    //}
                    //else
                    //    rowIT = this.ExportDMProperty(iRow_Detail_IT + 1, rowAF + dtData.Rows.Count - 1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                }

                int rowIT_Other = 0;
                if (dtITOther.Rows.Count > 0)
                {
                    rowIT_Other = this.ExportDMProperty(iRow_Detail_ITOther - irow, rowAF + rowAF_Other + rowIT + dtData.Rows.Count - 1, dtITOther, iMax_Colum, ws, arr_ITOther, arrIndexCol_ITOther, arrIndexFormular_ITOther, IndexRow_ITOther);
                }
                else
                {
                    rowIT_Other = this.ExportDMProperty(iRow_Detail_ITOther - irow, rowAF + rowAF_Other + rowIT + dtData.Rows.Count - 1, dtITOther, iMax_Colum, ws, arr_ITOther, arrIndexCol_ITOther, arrIndexFormular_ITOther, IndexRow_ITOther);
                    irow += 1;
                    //if (rowAF <= 0 && dtData.Rows.Count <= 0)
                    //{
                    //    rowIT = this.ExportDMProperty(iRow_Detail_IT - 1, rowAF + dtData.Rows.Count - 1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                    //}
                    //else if ((rowAF <= 0 && dtData.Rows.Count > 0) || rowAF > 0 && dtData.Rows.Count <= 0)
                    //{
                    //    rowIT = this.ExportDMProperty(iRow_Detail_IT, rowAF + dtData.Rows.Count - 1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                    //}
                    //else
                    //    rowIT = this.ExportDMProperty(iRow_Detail_IT + 1, rowAF + dtData.Rows.Count - 1, dtData_IT, iMax_Colum, ws, arr_IT, arrIndexCol_IT, arrIndexFormular_IT, IndexRow_IT);
                }

                int rowFA = 0;
                if (dtData_FA.Rows.Count > 0)
                {
                    rowFA = this.ExportDMProperty(iRow_Detail_FA, rowAF+rowAF_Other + rowIT+rowIT_Other + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                }
                else
                {
                    rowFA = this.ExportDMProperty(iRow_Detail_FA, rowAF + rowAF_Other + rowIT + rowIT_Other + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                    irow += 1;
                    //if (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0)
                    //{
                    //    rowFA = this.ExportDMProperty(iRow_Detail_FA - 2, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                    //}
                    //else if ((rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0))
                    //{
                    //    rowFA = this.ExportDMProperty(iRow_Detail_FA - 1, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                    //}
                    //else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0))
                    //{
                    //    rowFA = this.ExportDMProperty(iRow_Detail_FA, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                    //}
                    //else
                    //    rowFA = this.ExportDMProperty(iRow_Detail_FA + 1, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);


                }

                int rowFA_Other = 0;
                if (dtFAOther.Rows.Count > 0)
                {
                    rowFA_Other = this.ExportDMProperty(iRow_Detail_FAOther - irow, rowAF + rowIT + rowFA + rowIT_Other+rowAF_Other + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                }
                else
                {
                    rowFA_Other = this.ExportDMProperty(iRow_Detail_FAOther - irow, rowAF + rowIT + rowFA + rowIT_Other + rowAF_Other + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                    irow += 1;
                    //if (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0)
                    //{
                    //    rowFA = this.ExportDMProperty(iRow_Detail_FA - 2, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                    //}
                    //else if ((rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0))
                    //{
                    //    rowFA = this.ExportDMProperty(iRow_Detail_FA - 1, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                    //}
                    //else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0))
                    //{
                    //    rowFA = this.ExportDMProperty(iRow_Detail_FA, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);
                    //}
                    //else
                    //    rowFA = this.ExportDMProperty(iRow_Detail_FA + 1, rowAF + rowIT + dtData.Rows.Count - 2, dtData_FA, iMax_Colum, ws, arr_FA, arrIndexCol_FA, arrIndexFormular_FA, IndexRow_FA);


                }

                int rowHR = 0;
                if (dtData_HR.Rows.Count > 0)
                {
                    rowHR = this.ExportDMProperty(iRow_Detail_HR-irow, rowIT + rowIT_Other + rowFA + rowFA_Other + rowAF + rowAF_Other + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                }
                else
                {
                    rowHR = this.ExportDMProperty(iRow_Detail_HR - irow, rowIT + rowIT_Other + rowFA + rowFA_Other + rowAF + rowAF_Other + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    irow += 1;
                    //if (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0)
                    //{
                    //    rowHR = this.ExportDMProperty(iRow_Detail_HR - 3, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    //}
                    //else if ((rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0))
                    //{
                    //    rowHR = this.ExportDMProperty(iRow_Detail_HR - 2, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    //}
                    //else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0))
                    //{
                    //    rowHR = this.ExportDMProperty(iRow_Detail_HR - 1, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    //}
                    //else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA > 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0) || (rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0))
                    //    rowHR = this.ExportDMProperty(iRow_Detail_HR, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    //else
                    //    rowHR = this.ExportDMProperty(iRow_Detail_HR + 1, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);

                }

                int rowHRTraining = 0;
                if (dtHRTraining.Rows.Count > 0)
                {
                    rowHRTraining = this.ExportDMProperty(iRow_Detail_HRTraining - irow, rowHR+ rowIT + rowIT_Other + rowFA + rowFA_Other + rowAF + rowAF_Other + dtData.Rows.Count - 3, dtHRTraining, iMax_Colum, ws, arr_HRTraining, arrIndexCol_HRTraining, arrIndexFormular_HRTraining, IndexRow_HRTraining);
                }
                else
                {
                    rowHRTraining = this.ExportDMProperty(iRow_Detail_HRTraining - irow, rowHR + rowIT + rowIT_Other + rowFA + rowFA_Other + rowAF + rowAF_Other + dtData.Rows.Count - 3, dtHRTraining, iMax_Colum, ws, arr_HRTraining, arrIndexCol_HRTraining, arrIndexFormular_HRTraining, IndexRow_HRTraining);
                    irow += 1;
                    //if (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0)
                    //{
                    //    rowHR = this.ExportDMProperty(iRow_Detail_HR - 3, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    //}
                    //else if ((rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0))
                    //{
                    //    rowHR = this.ExportDMProperty(iRow_Detail_HR - 2, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    //}
                    //else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0))
                    //{
                    //    rowHR = this.ExportDMProperty(iRow_Detail_HR - 1, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    //}
                    //else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA > 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0) || (rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0))
                    //    rowHR = this.ExportDMProperty(iRow_Detail_HR, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);
                    //else
                    //    rowHR = this.ExportDMProperty(iRow_Detail_HR + 1, rowIT + rowFA + rowAF + dtData.Rows.Count - 3, dtData_HR, iMax_Colum, ws, arr_HR, arrIndexCol_HR, arrIndexFormular_HR, IndexRow_HR);

                }

                int rowOther = 0;
                if (dtData_Other.Rows.Count > 0)
                {
                    rowOther = this.ExportDMProperty(iRow_Detail_Other-irow, rowHRTraining+ rowHR + rowFA +rowFA_Other + rowAF+rowAF_Other + rowIT+rowIT_Other + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                }
                else
                {
                    rowOther = this.ExportDMProperty(iRow_Detail_Other - irow, rowHRTraining + rowHR + rowFA + rowFA_Other + rowAF + rowAF_Other + rowIT + rowIT_Other + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                    irow += 1;
                    //if (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0 && rowHR <= 0)
                    //{
                    //    rowOther = this.ExportDMProperty(iRow_Detail_Other - 4, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                    //}
                    //else if ((rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA <= 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0 && rowHR > 0))
                    //{
                    //    rowOther = this.ExportDMProperty(iRow_Detail_Other - 3, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                    //}
                    //else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0 && rowHR <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA <= 0 && rowHR <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0 && rowHR <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA <= 0 && rowHR > 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA <= 0 && rowHR > 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count <= 0 && rowIT <= 0 && rowFA > 0 && rowHR > 0))
                    //{
                    //    rowOther = this.ExportDMProperty(iRow_Detail_Other - 2, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                    //}
                    //else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA > 0 && rowHR <= 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0 && rowHR <= 0) || (rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0 && rowHR <= 0))
                    //    rowOther = this.ExportDMProperty(iRow_Detail_Other - 1, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                    //else if ((rowAF > 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA > 0 && rowHR <= 0) || (rowAF <= 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA > 0 && rowHR > 0) || (rowAF > 0 && dtData.Rows.Count <= 0 && rowIT > 0 && rowFA > 0 && rowHR > 0) || (rowAF > 0 && dtData.Rows.Count > 0 && rowIT <= 0 && rowFA > 0 && rowHR > 0) || (rowAF > 0 && dtData.Rows.Count > 0 && rowIT > 0 && rowFA <= 0 && rowHR > 0))
                    //    rowOther = this.ExportDMProperty(iRow_Detail_Other, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);
                    //else
                    //    rowOther = this.ExportDMProperty(iRow_Detail_Other + 1, rowHR + rowFA + rowAF + rowIT + dtData.Rows.Count - 4, dtData_Other, iMax_Colum, ws, arr_Other, arrIndexCol_Other, arrIndexFormular_Other, IndexRow_Other);

                }
                #region replace parametter
                ArrayList arrReplace = new ArrayList();
                if (dtReplace != null)
                {
                    for (int i = 0; i < dtReplace.Columns.Count; i++)
                    {
                        string sColumnName = dtReplace.Columns[i].ToString();
                        object sColumnValue = dtReplace.Rows[0][sColumnName];

                        Cell c = ws.Cells.FindString("$" + sColumnName + "$", null);
                        if (c == null)
                            ws.Replace("$" + sColumnName + "$", sColumnValue.ToString());
                        else
                            c.PutValue(sColumnValue);
                    }
                }
                #endregion

                #region Password
                if (sPassword.Trim() != "")
                    ws.Protect(ProtectionType.All, sPassword, null);
                #endregion

                ws.Cells.DeleteRow(iRow_Group1);
                ws.Cells.DeleteRow(iRow_Group1);

                wb.Worksheets.RemoveAt(1);
                // wb.Worksheets.RemoveAt(1);


                MemoryStream Stream = new MemoryStream();
                FileFormatType Format = new Aspose.Cells.FileFormatType();

                if (sVersionExcel == "2007")
                {
                    Format = Aspose.Cells.FileFormatType.Excel2007Xlsx;
                    sFileNameExcel += ".xlsx";
                }

                else
                {
                    Format = Aspose.Cells.FileFormatType.Excel2003;
                    sFileNameExcel += ".xls";
                }
                wb.Save(Stream, Format);
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + sFileNameExcel);
                HttpContext.Current.Response.BinaryWrite(Stream.ToArray());
                HttpContext.Current.Response.End();

                return "";
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
            return "";
        }
        #region xuất danh mục đã tồn tại
        public int ExportDMProperty(int rowDetail, int rowBefore, DataTable datAF, int maxColumn, Worksheet ws, ArrayList arr_add
                , ArrayList arrIndexCol_add
                , ArrayList arrIndexFormular_add
                , int IndexRow_add)
        {
            try
            {
                //begin
                if (datAF != null)
                {

                    if (datAF.Rows.Count > 0)
                    {
                        #region insert cac detail
                        for (int i = 0; i < arr_add.Count; i++)
                        {
                            if (i == 0)
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(datAF, false, IndexRow_add + rowBefore - 1, Convert.ToInt16(arrIndexCol_add[i]), arr_add[i].ToString(), true);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow_add, Convert.ToInt16(arrIndexCol_add[i])].PutValue(null);
                                }
                            }
                            else
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(datAF, false, IndexRow_add + rowBefore - 1, Convert.ToInt16(arrIndexCol_add[i]), arr_add[i].ToString(), false);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow_add, Convert.ToInt16(arrIndexCol_add[i])].PutValue(null);
                                }
                            }
                        }

                        ws.Cells.DeleteRow(IndexRow_add + rowBefore + datAF.Rows.Count-1);

                        //--Sao chep cong thuc

                        for (int i = 0; i < arrIndexFormular_add.Count; i++)
                        {
                            Cell sTmp = ws.Cells[rowDetail, Convert.ToInt16(arrIndexFormular_add[i])];

                            for (int j = rowDetail + 1; j < rowDetail; j++)
                            {
                                ws.Cells[j, Convert.ToInt16(arrIndexFormular_add[i])].Copy(sTmp);
                            }
                        }
                        ////--End

                        //ws.Cells.DeleteRow(IndexRow_add + rowBefore + datAF.Rows.Count - 1);
                        #endregion

                    }
                    else
                        ws.Cells.DeleteRow(rowDetail);

                }
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
            return datAF.Rows.Count;
        }
        #endregion
        #region xuất danh mục chưa tồn tại
        public int ExportNotDMProperty(int rowDetail, int rowBefore, DataTable datAF, int maxColumn, Worksheet ws, ArrayList arr_add
                , ArrayList arrIndexCol_add
                , ArrayList arrIndexFormular_add
                , int IndexRow_add)
        {
            try
            {

                //begin
                if (datAF != null)
                {

                    if (datAF.Rows.Count > 0)
                    {
                        #region insert cac detail
                        for (int i = 0; i < arr_add.Count; i++)
                        {
                            if (i == 0)
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(datAF, false, IndexRow_add + rowBefore - 1, Convert.ToInt16(arrIndexCol_add[i]), arr_add[i].ToString(), true);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow_add, Convert.ToInt16(arrIndexCol_add[i])].PutValue(null);
                                }
                            }
                            else
                            {
                                try
                                {
                                    ws.Cells.ImportDataColumn(datAF, false, IndexRow_add + rowBefore - 1, Convert.ToInt16(arrIndexCol_add[i]), arr_add[i].ToString(), false);
                                }
                                catch
                                {
                                    ws.Cells[IndexRow_add, Convert.ToInt16(arrIndexCol_add[i])].PutValue(null);
                                }
                            }
                        }

                        //--Sao chep cong thuc

                        for (int i = 0; i < arrIndexFormular_add.Count; i++)
                        {
                            Cell sTmp = ws.Cells[rowDetail, Convert.ToInt16(arrIndexFormular_add[i])];

                            for (int j = rowDetail + 1; j < rowDetail; j++)
                            {
                                ws.Cells[j, Convert.ToInt16(arrIndexFormular_add[i])].Copy(sTmp);
                            }
                        }
                        //--End

                        ws.Cells.DeleteRow(IndexRow_add + rowBefore + datAF.Rows.Count - 1);
                        #endregion

                    }
                    else
                        ws.Cells.DeleteRow(rowDetail + 1);

                }
            }
            catch (Exception ex)
            {
                AlertMessage message = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(message);
            }
            return datAF.Rows.Count;
        }
        #endregion
        #endregion

        /// <summary>
        /// xử lý sự kiện khi nhấn button xuất dl
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnBottomExport_Click(object sender, EventArgs e)
        {
            try
            {
                if (this.EmpHeader.EmployeeId > 0)
                {
                    LS_ExitFormCollection cl = new LS_ExitFormCollection();
                    DataTable datManager = new DataTable();
                    DataTable datAF = new DataTable();
                    DataTable datAFOther = new DataTable();
                    DataTable datIT = new DataTable();
                    DataTable datITOther = new DataTable();
                    DataTable datFA = new DataTable();
                    DataTable datFAOther = new DataTable();
                    DataTable datHR = new DataTable();
                    DataTable datHRTraining = new DataTable();
                    DataTable datOther = new DataTable();
                    DataTable dtReplace = new DataTable();
                    datManager = cl.GetExitContain(1, this.EmpHeader.EmployeeId, 0, 0);

                    datManager = cl.GetExitContain(1, this.EmpHeader.EmployeeId, 0, 0);

                    datAF = cl.LoadViewDMProperty(2, this.EmpHeader.EmployeeId, 0, 0);

                    datAFOther = cl.GetExitContain(11, this.EmpHeader.EmployeeId, 0, 0);

                    datIT = cl.LoadViewDMProperty(1, this.EmpHeader.EmployeeId, 0, 0);

                    datITOther = cl.GetExitContain(12, this.EmpHeader.EmployeeId, 0, 0);

                    datFA = cl.GetExitContain(4, this.EmpHeader.EmployeeId, 0, 0);

                    datFAOther = cl.GetExitContain(13, this.EmpHeader.EmployeeId, 0, 0);

                    datHR = cl.LoadViewDMProperty(3, this.EmpHeader.EmployeeId, 0, 0);

                    datHRTraining = cl.GetExitContain(10, this.EmpHeader.EmployeeId, 0, 0);

                    datOther = cl.GetExitContain(8, this.EmpHeader.EmployeeId, 0, 0);

                    dtReplace = cl.LoadEmpInfo(EmpHeader.EmployeeId);
                    //  dtReplace = cl.SelectToReplace(hdfDevelopedPlanID.Value);
                    string filePath = this.Server.MapPath(HR_ConfigManager.TemplateReportHR + _FileName);
                    ExportExcel(filePath, datManager, datAF, datIT, datFA, datHR, datOther, dtReplace,datAFOther,datITOther,datFAOther,datHRTraining);
                }
                else
                {
                    MessageBox.ShowByMessCode("10095", MessageType.Error);
                }
            }
            catch (Exception ex)
            {
                AlertMessage mess = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(mess);
            }
        }


        protected void dtgDetail_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e)
        {
            try
            {
                if (e.Item is GridDataItem)
                {
                    GridDataItem item = (GridDataItem)e.Item;
                    RadDatePicker dpkTungay = (RadDatePicker)item.FindControl("txtRecoveryDate");
                    try
                    {
                        DateTime todate =Convert.ToDateTime(item["ToDate"].Text.Trim());
                        dpkTungay.SelectedDate = todate;
                    }
                    catch
                    {
                        dpkTungay.SelectedDate = null;
                    }
                }
            }
            catch (Exception ex)
            {
                AlertMessage mess = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(mess);
            }
        }

        protected void dtgIT_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e)
        {
            try
            {
                if (e.Item is GridDataItem)
                {
                    GridDataItem item = (GridDataItem)e.Item;
                    RadDatePicker dpkTungay = (RadDatePicker)item.FindControl("txtRecoveryDate");
                    try
                    {
                        DateTime todate = Convert.ToDateTime(item["ToDate"].Text.Trim());
                        dpkTungay.SelectedDate = todate;
                    }
                    catch
                    {
                        dpkTungay.SelectedDate = null;
                    }
                }
            }
            catch (Exception ex)
            {
                AlertMessage mess = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(mess);
            }
        }

        protected void dtgHRExit_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e)
        {
            try
            {
                if (e.Item is GridDataItem)
                {
                    GridDataItem item = (GridDataItem)e.Item;
                    RadDatePicker dpkTungay = (RadDatePicker)item.FindControl("txtRecoveryDate");
                    try
                    {
                        DateTime todate = Convert.ToDateTime(item["ToDate"].Text.Trim());
                        dpkTungay.SelectedDate = todate;
                    }
                    catch
                    {
                        dpkTungay.SelectedDate = null;
                    }
                }
            }
            catch (Exception ex)
            {
                AlertMessage mess = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(mess);
            }
        }

        protected void dtgAFExit_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e)
        {
            try
            {
                if (e.Item is GridDataItem)
                {
                    GridDataItem item = (GridDataItem)e.Item;
                    RadDatePicker dpkTungay = (RadDatePicker)item.FindControl("txtRecoveryDate");
                    try
                    {
                        DateTime todate = Convert.ToDateTime(item["ToDate"].Text.Trim());
                        dpkTungay.SelectedDate = todate;
                    }
                    catch
                    {
                        dpkTungay.SelectedDate = null;
                    }
                }
            }
            catch (Exception ex)
            {
                AlertMessage mess = UserInterfaceExceptionHandler.HandleExcetion(ex);
                MessageBox.Show(mess);
            }
        }
    }

    public enum ExitType
    {
        IT = 1,
        ITOther = 12,
        AF = 2,
        AFOther = 11,
        FA = 4,
        FAOther = 13,
        HR = 3,
        HRTraining = 10,
        HROther = 8,
    }
}